import time

from Common.do_mysql import DoMysql
from Data.common_data import Commondata
from Common.commontool import CommonTool


class BusinessCommonTools(object):

    def  get_verification_code(self,cmdopt,database_object):
        time.sleep(5)
        database=Commondata.db_config[cmdopt]['database'][0]
        query_verification_code=Commondata.query_user_validation_code_sql
        #query_sql=query_verification_code.replace('@a',email)
        data_object,pool=CommonTool(database_object).get_data_source(database,cmdopt)
        query_result=data_object.do_mysql(query_verification_code,pool)
        #print(query_result)
        #code=query_result[0]['validation_code']
        return query_result

    def get_car_list(self,cmdopt,start_date,end_date,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_car_list = Commondata.query_car_list
        query_amount_list = Commondata.query_amount
        query_verification_code = Commondata.query_user_validation_code_sql
        query_sql=query_car_list.replace('@a',start_date).replace('@b', end_date)
        query_amount_list_sql = query_amount_list.replace('@a',start_date).replace('@b', end_date)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_result1 = data_object.do_mysql(query_sql, pool)
        query_result2 = data_object.do_mysql(query_verification_code, pool)
        amount_list = data_object.do_mysql(query_amount_list_sql, pool)
        return query_result1,query_result2,amount_list

    # result1货主数，result2 货主列表，
    def get_consignor_and_cooperation_list(self,cmdopt,start_date,end_date,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 货主
        query_consignor_sum = Commondata.query_consignor_sum
        query_consignor_list = Commondata.duery_consignor_list.replace('@a',start_date).replace('@b', end_date)
        # 企业线路
        query_logistics_line = Commondata.query_logistics_line
        query_logistics_line_today = Commondata.query_logistics_line_today.replace('@a',start_date).replace('@b',end_date)
        # 交易票数
        query_waybill_list_today = Commondata.query_waybill_list_today2.replace('@a',start_date).replace('@b',end_date)
        query_waybill_end_sum = Commondata.query_waybill_end_sum

        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        result1 = data_object.do_mysql(query_consignor_sum, pool)
        result2 = data_object.do_mysql(query_consignor_list, pool)
        result3 = data_object.do_mysql(query_logistics_line, pool)
        result4 = data_object.do_mysql(query_logistics_line_today, pool)
        result5 = data_object.do_mysql(query_waybill_end_sum, pool)
        result6 = data_object.do_mysql(query_waybill_list_today, pool)
        return result1,result2,result3,result4,result5,result6

        # result1货主数，result2 货主列表，

    def get_waybill_list(self, cmdopt, start_date, end_date, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_waybill_list = Commondata.query_waybill_list_today2.replace('@a', start_date).replace('@b',end_date)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)
        return query_waybill_list

    # 获取物流线路报价
    def get_logistics_line(self, cmdopt, forwarding_province,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_logistics_line = Commondata.query_logistics_line_new.replace('@a', forwarding_province)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_logistics_line = data_object.do_mysql(query_logistics_line, pool)
        return query_logistics_line

    # 获取7月票详细
    def get_query_waybill_list(self, cmdopt ,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_waybill_list = Commondata.query_waybill_list
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)
        return query_waybill_list

    # 获取7月票详细 2
    def get_query_waybill_list2(self, cmdopt ,time,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_waybill_list = Commondata.query_waybill_list1.replace('@a', time)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)
        return query_waybill_list

    # 销售报表
    def get_query_waybill_list_xiaoshou(self, cmdopt ,time,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_waybill_list = Commondata.query_waybill_list_xiaoshou.replace('@a', time)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)
        return query_waybill_list


    def get_consignor20241230(self,cmdopt,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 货主
        query_consignor_list = Commondata.consignor_list_all
        # 企业线路
        query_logistics_line = Commondata.waybill_list_all
        # 司机
        query_driver_line = Commondata.driver_list_all

        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        consignor_list = data_object.do_mysql(query_consignor_list, pool)
        logistics_line = data_object.do_mysql(query_logistics_line, pool)
        query_driver_line = data_object.do_mysql(query_driver_line, pool)

        return consignor_list,logistics_line,query_driver_line


    # 根据订单号查询
    def waybill_search(self, cmdopt, q,h, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        print(q,h)
        str1 = q
        # 交易票数
        if q[:2]=="YD" or (q[:2]!='WL' and q[:2]!='SJ'):
            data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        elif q[:2]=='WL':
            yd_by_wl = Commondata.yd_by_wl.replace('@a', q)
            data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
            q_list = data_object.do_mysql(yd_by_wl, pool)
            if q_list:
                q=q_list[0]['waybill_code']
                str1=q
        elif q[:2]=='SJ':
            yd_by_sj = Commondata.yd_by_sj.replace('@a', q)
            data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
            q_list = data_object.do_mysql(yd_by_sj, pool)
            if q_list:
                q = q_list[0]['code']
                str1 = q
        if h:
            yd_by_phone = Commondata.yd_by_phone.replace('@a', h.strip())
            data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
            h_list = data_object.do_mysql(yd_by_phone, pool)
            print('############')
            if h_list:
                result = "','".join([f"{item['CODE']}" for item in h_list])
                print(result)
                str1 = result


        query_waybill_list = Commondata.waybill_search.replace('@a',str1 )
        print(query_waybill_list)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)

        pool.close()
        return query_waybill_list

    # 20250703 按月获取司机
    def get_driver(self,cmdopt,time,sql,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 司机
        query_driver_line = sql.replace('@a', time)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_driver_line = data_object.do_mysql(query_driver_line, pool)
        return query_driver_line


    # 20250705 获取司机单的协议
    def get_driver_agreement(self,cmdopt,sj,sql,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 司机
        query_driver_agreement = sql.replace('@a', sj)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_driver_agreement = data_object.do_mysql(query_driver_agreement, pool)
        return query_driver_agreement

    # 20250707 获取司机行驶证号等
    def get_driver_info(self,cmdopt,sj,sql,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 司机
        query_driver_agreement = sql.replace('@a', sj[0].lstrip().rstrip()).replace('@b', sj[1].lstrip().rstrip())
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_driver_agreement = data_object.do_mysql(query_driver_agreement, pool)
        return query_driver_agreement


    # 获取司机账单
    def get_query_driver_bill(self, cmdopt, user_id,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_driver_bill = Commondata.search_driver_bill.replace('@a', user_id)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_driver_bill = data_object.do_mysql(query_driver_bill, pool)
        return query_driver_bill

    #获取司机id
    def get_query_driver_id(self, cmdopt, name,sql,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_driver_id = sql.replace('@a', name)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_driver_id = data_object.do_mysql(query_driver_id, pool)
        print(query_driver_id)
        return query_driver_id


    ##### 20250903 修改哈密瓜#####
    ###################################
    # 查询主干数据
    def get_query_waybill_list3(self, cmdopt ,time,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_waybill_list = Commondata.sql1.replace('@a', time)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)
        return query_waybill_list

    # 查询货物重量
    def get_query_search_cargo(self, cmdopt, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        # 交易票数
        query_waybill_list = Commondata.search_cargo
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_waybill_list = data_object.do_mysql(query_waybill_list, pool)
        return query_waybill_list